import pandas as pd
import sys
import os
import mysql.connector


def detect_order_column(columns):
    candidates = [
        "系统订单号",
        "订单号",
        "系统订单编号",
        "EOOR_ORDER_NO",
        "eoor_order_no",
    ]
    lower_map = {str(c).strip().lower(): c for c in columns}
    for name in candidates:
        key = name.strip().lower()
        if key in lower_map:
            return lower_map[key]
    # fuzzy contains
    for c in columns:
        cl = str(c).strip().lower()
        if any(k in cl for k in ["系统订单", "订单号", "order_no", "订单编号"]):
            return c
    return None


def read_all_order_nos(excel_path: str) -> list:
    if not os.path.exists(excel_path):
        raise FileNotFoundError(f"Excel文件不存在: {excel_path}")
    df = pd.read_excel(excel_path, sheet_name="Sheet1")
    if df.empty:
        return []
    order_col = detect_order_column(df.columns)
    if not order_col:
        raise ValueError("未识别到‘系统订单号’列，请确认列名。")
    series = df[order_col].astype(str).str.strip()
    series = series.replace({"": pd.NA, "nan": pd.NA, "None": pd.NA})
    values = series.dropna().tolist()
    # 去重，保持原有顺序
    seen = set()
    unique_orders = []
    for v in values:
        if v not in seen:
            seen.add(v)
            unique_orders.append(v)
    return unique_orders


def update_order_status_batch(
    order_nos: list,
    host: str,
    port: int,
    user: str,
    password: str,
    database: str,
):
    if not order_nos:
        return 0
    conn = mysql.connector.connect(
        host=host, port=port, user=user, password=password, database=database
    )
    try:
        cur = conn.cursor()
        total_affected = 0
        batch_size = 1000
        for i in range(0, len(order_nos), batch_size):
            batch = order_nos[i:i + batch_size]
            placeholders = ",".join(["%s"] * len(batch))
            sql = (
                "update eo_t_order eoor set eoor.EOOR_STATUS_CODE ='70' , eoor.MODIFY_TIME = NOW() "
                f"where eoor.EOOR_ORDER_NO in ({placeholders})"
            )
            cur.execute(sql, tuple(batch))
            total_affected += cur.rowcount
            conn.commit()
        return total_affected
    finally:
        try:
            cur.close()
        except Exception:
            pass
        conn.close()


def main():
    # Defaults from user request
    excel_path = "/Users/qingjunluo/Desktop/未达帐报表.xlsx"
    host = "172.24.222.20"
    port = 2281
    user = "a_comm_prd"
    password = "c4F!9opd"
    database = "asd_lmdm_prd"

    # Optional overrides via CLI: excel_path
    # Example: python update_order_status_from_excel.py /path/file.xlsx
    if len(sys.argv) >= 2:
        excel_path = sys.argv[1]

    print(f"读取Excel: {excel_path} -> Sheet1，提取全部系统订单号…")
    order_nos = read_all_order_nos(excel_path)
    print(f"共提取到 {len(order_nos)} 个订单号（去重后）。")
    if not order_nos:
        print("未获取到任何订单号，停止。")
        sys.exit(0)

    print("开始按批次更新数据库状态为70…")
    affected = update_order_status_batch(order_nos, host, port, user, password, database)
    print(f"更新完成，总影响行数: {affected}")


if __name__ == "__main__":
    main()


